<?php

function list_laporan( $yearly_income_id ){
  
  
	
	$nik_start = isset($_GET['nik_start']) ? sprintf('%07d',(int)$_GET['nik_start']) :'0000001'; 	
	$nik_end = (isset($_GET['nik_end']) AND (int) $_GET['nik_end'] !='0' )? sprintf('%07d',(int)$_GET['nik_end']) :'9999999'; 	

	$header = array(
		'No'=>array('style'=>'border-bottom:2px solid;width:5%'),  
		'NIK'=>array('style'=>'border-bottom:2px solid;width:20%'),  
		'Name'=>array('style'=>'border-bottom:2px solid;width:35%'),  
		'Penerimaan'=>array('style'=>'border-bottom:2px solid;width:15%'),  
		'Pengurangan'=>array('style'=>'border-bottom:2px solid;width:15%'),  
		'JUMLAH PPh 21'=>array('style'=>'text-align:right;border-bottom:2px solid;width:15%'),  
	);
	
	$query = "SELECT * FROM   karyawan a  
		INNER JOIN  pr_yearly_income_pajak f ON f.karyawan_id = a.karyawan_id
		WHERE f.pr_yearly_income_id={$yearly_income_id}	
		AND ( a.nik BETWEEN {$nik_start}	AND {$nik_end} ) 
		AND a.karyawan_id NOT IN (select karyawan_id FROM pemberhentian  ) 
		AND a.karyawan_id NOT IN (select karyawan_id FROM karyawan_disable)
		GROUP BY f.karyawan_id ORDER BY a.nik"; 
	 
	$result = my_query($query );
	$total_records = my_num_rows($result );
	$scroll_page =5;  
	$per_page =15;  
	$current_page = isset($_GET['page']) ? (int) $_GET['page'] : 1 ; 
	if($current_page < 1){
		$current_page = 1;
	}		 
	$pager_url  ="index.php?com={$_GET['com']}&periode_id={$periode_id}&nik_start={$nik_start}&nik_end={$nik_end}&task={$_GET['task']}&page=";	 
	$inactive_page_tag = 'style="padding:4px;background-color:#BBBBBB"';  
	$previous_page_text = ' Mundur '; 
	$next_page_text = ' Maju ';  
	$first_page_text = ' Awal '; 
	$last_page_text = ' Akhir ';
	
	$kgPagerOBJ = new kgPager();
	$kgPagerOBJ->pager_set(
		$pager_url, 
		$total_records, 
		$scroll_page, 
		$per_page, 
		$current_page, 
		$inactive_page_tag, 
		$previous_page_text, 
		$next_page_text, 
		$first_page_text, 
		$last_page_text ,$pager_url_last); 
	 		
	$result = my_query($query ." LIMIT ".$kgPagerOBJ->start.", ".$kgPagerOBJ->per_page); 	
	$row = array(); 
	$i = ($current_page  - 1 ) * 15; 
	while($ey = my_fetch_array($result )){
		$i++;
		$karyawan =  loaddata_karyawan($ey['karyawan_id']);  
		$jabatan =  loaddata_jabatan($karyawan['jabatan_id']);   
		
			$row[] = array(
				'#'=>position_text_align($i,'center'),
				'nik'=>  $karyawan['nik'] ,
				'nama'=>  $karyawan['nama_gelar'] ,
				'penerimaan'=>  position_text_align ( rp_format($ey['pajak_07'] ),'right'),
				'pengeluaram'=>  position_text_align ( rp_format($ey['pajak_13'] ),'right'),
				'amount'=>  position_text_align (rp_format($ey['pajak_23']) ,'right'),   
			);  
	}
	
	$datas = table_rows($row); 
	$paging = $kgPagerOBJ ->showPaging();
	$search = '<form method="GET"><input type="hidden" name="com" value="'.$_GET['com'].'" />
	<span style="font:10px verdana"><b>Pencarian cepat</b></span>
	<br/><input type="text"  style="width:200px;" name="key" value="'.(isset($_GET['key']) ? $_GET['key']: "").'" />
	<input class="simple_search" type="submit" value=" CARI " /> </form>';
	$view = form_header( "..." , "..."  );
	$query_check_periode = " SELECT b.label as yin , a.pr_yearly_income_id , a.tahun FROM pr_yearly_income a
	INNER JOIN yearly_income b ON a.yearly_income_id = b.yearly_income_id
		   ORDER BY pr_yearly_income_id DESC";
	 
	$result = my_query($query_check_periode);
	$prs = array();
	while($prow = my_fetch_array($result)){
		$prs[$prow['pr_yearly_income_id']] =   $prow['yin']  .' '. $prow['tahun'] ;
	}
	$pr_yearly_income_id = array(
		'id'=>'pr_yearly_income_id',
		'value'=>$pr_yearly_income_id ,
		'name'=>'pr_yearly_income_id',
	);
	$view .= '<input type="hidden"  value="'.$_GET['com'].'" name="com" />';
	$view .= form_field_display( form_dropdown($pr_yearly_income_id,$prs)   , "Yearly income"   );	 
	$form_start = '<input type="text" size="7" value="'.$nik_start.'" name="nik_start" />';
	$form_end = '<input type="text" size="7" value="'.$nik_end.'" name="nik_end" />';
	$view .= form_field_display( $form_start.' s/d '.$form_end  , "NIK Range"   );
	$submit = array(
		'value' => (  ' Check '  ),
		'name' => 'simpan', 
		'type'=>'submit','class'=>'main_button'
	);
	$form_submit= form_dynamic($submit); 	
	$cetak = array(
		'value' => (  ' Cetak '  ),
		'onclick'=>'javascript:window.open(\'index.php?com=report&task=txt_list_of_transfer&periode_id='.$periode_id.'&nik_start='.$nik_start.'&nik_end='.$nik_end.'\',\'mywin\',\'left=20,top=20,width=800,height=600,toolbar=0,scrollbars,resizable=1\');',
		'type'=>'button','class'=>'main_button'
	);
	$form_cetak= form_dynamic($cetak); 	
 
	$excel = array(
		'value' => (  ' Excel '  ),
		'onclick' => 'javascript:location.href=\'index.php?com='.$_GET['com'].'&task=excel_list_of_transfer&pr_yearly_income_id='.$yearly_income_id.'&nik_start='.$nik_start.'&nik_end='.$nik_end.'\'', 
		'type'=>'button','class'=>'main_button'
	);
	$form_excel= form_dynamic($excel); 
	$view .= form_field_display( $form_submit .'  '.$form_excel/*.'  '.$form_cetak */ , "&nbsp;" ,  "" );
	$view .= form_footer( );
	$view = str_replace("method=\"post\"","method=\"get\"",$view);
	return $view.table_builder($header , $datas ,  15 ,false , $paging  );
} 


function xl_list_spt($yearly_income_id){
	  
	$nik_start = isset($_GET['nik_start']) ? sprintf('%07d',(int)$_GET['nik_start']) :'0000001'; 	
	$nik_end = (isset($_GET['nik_end']) AND (int) $_GET['nik_end'] !='0' )? sprintf('%07d',(int)$_GET['nik_end']) :'9999999'; 	

	$header = array(
		'No'=>array('style'=>'border-bottom:2px solid;width:5%'),  
		'NIK'=>array('style'=>'border-bottom:2px solid;width:20%'),  
		'Name'=>array('style'=>'border-bottom:2px solid;width:40%'),  
		'GAJI / PENSIUN ATAU THT / JHT'=>array('style'=>'border-bottom:2px solid;width:30%'),  
		'Tunjangan PPh'=>array('style'=>'text-align:right;border-bottom:2px solid;width:30%'),  
		'TUNJANGAN LAINNYA, UANG LEMBUR, DAN SEBAGAINYA'=>array('style'=>'text-align:right;border-bottom:2px solid;width:30%'),  
		'HONORARIUM DAN IMBALAN LAIN SEJENISNYA'=>array('style'=>'text-align:right;border-bottom:2px solid;width:30%'),  
		'PREMI ASURANSI YANG DIBAYAR PEMBERI KERJA'=>array('style'=>'text-align:right;border-bottom:2px solid;width:30%'),  
		'PENERIMAAN DALAM BENTUK NATURA DAN KENIKMATAN LAIN'=>array('style'=>'text-align:right;border-bottom:2px solid;width:30%'),  
		'JUMLAH (1 s.d. 6)'=>array('style'=>'text-align:right;border-bottom:2px solid;width:30%'),  
		'TANTIEM, BONUS, GRATIFIKASI, JASA PRODUKSI, DAN TH'=>array('style'=>'text-align:right;border-bottom:2px solid;width:30%'),  
		'JUMLAH PENGHASILAN BRUTO (7 + 8)'=>array('style'=>'text-align:right;border-bottom:2px solid;width:30%'),  
		'BIAYA JABATAN / BIAYA PENSIUN ATAS PENGHASILAN PADA ANGKA 7'=>array('style'=>'text-align:right;border-bottom:2px solid;width:30%'),  
		'BIAYA JABATAN / BIAYA PENSIUN ATAS PENGHASILAN PADA ANGKA 8'=>array('style'=>'text-align:right;border-bottom:2px solid;width:30%'),  
		'IURAN PENSIUN ATAU IURAN THT/ JHT'=>array('style'=>'text-align:right;border-bottom:2px solid;width:30%'),  
		'JUMLAH PENGURANGAN (10 + 11 + 12)'=>array('style'=>'text-align:right;border-bottom:2px solid;width:30%'),  
		'JUMLAH PENGHASILAN NETO (9 - 13)'=>array('style'=>'text-align:right;border-bottom:2px solid;width:30%'),  
		'PENGHASILAN NETO MASA SEBELUMNYA'=>array('style'=>'text-align:right;border-bottom:2px solid;width:30%'),  
		'JUMLAH PENGHASILAN NETO UNTUK PENGHITUNGAN PPh PASAL 21 (SETAHUN/DISETAHUNKAN)'=>array('style'=>'text-align:right;border-bottom:2px solid;width:30%'),  
		'PENGHASILAN TIDAK KENA PAJAK (PTKP)'=>array('style'=>'text-align:right;border-bottom:2px solid;width:30%'),  
		'PENGHASILAN KENA PAJAK SETAHUN / DISETAHUNKAN'=>array('style'=>'text-align:right;border-bottom:2px solid;width:30%'),  
		'PPh PASAL 21 ATAS PENGHASILAN KENA PAJAK SETAHUN/DISETAHUNKAN'=>array('style'=>'text-align:right;border-bottom:2px solid;width:30%'),  
		'PPh PASAL 21 YANG TELAH DIPOTONG MASA SEBELUMNYA'=>array('style'=>'text-align:right;border-bottom:2px solid;width:30%'),  
		'PPh PASAL 21 TERUTANG'=>array('style'=>'text-align:right;border-bottom:2px solid;width:30%'),  
		'PPh PASAL 21 DAN PPh PASAL 26 YANG TELAH DIPOTONG '=>array('style'=>'text-align:right;border-bottom:2px solid;width:30%'),  
		'JUMLAH PPh PASAL 21'=>array('style'=>'text-align:right;border-bottom:2px solid;width:30%'),  
	);
	 
	$query = "SELECT * FROM   karyawan a  
		INNER JOIN  pr_yearly_income_pajak f ON f.karyawan_id = a.karyawan_id
		WHERE f.pr_yearly_income_id={$yearly_income_id}	
		AND ( a.nik BETWEEN {$nik_start}	AND {$nik_end} ) 
		AND a.karyawan_id NOT IN (select karyawan_id FROM karyawan_disable)
		AND a.karyawan_id NOT IN (select karyawan_id FROM pemberhentian  ) 
		GROUP BY f.karyawan_id ORDER BY a.nik "; 
		 
	$result = my_query($query);
	while($ey = my_fetch_array($result )){
		$i++;
		$karyawan =  loaddata_karyawan($ey['karyawan_id']);  
		$jabatan =  loaddata_jabatan($karyawan['jabatan_id']);   

		$row[] = array(
			'#'=>position_text_align($i,'center'),
			'nik'=>  "'".$karyawan['nik'] ,
			'nama'=>  $karyawan['nama_gelar'] , 
			
			'pajak_01'=>  $ey['pajak_01'] , 
			'pajak_02'=>  $ey['pajak_02'] , 
			'pajak_03'=>  $ey['pajak_03'] , 
			'pajak_04'=>  $ey['pajak_04'] , 
			
			'pajak_05'=>  $ey['pajak_05'] , 
			'pajak_06'=>  $ey['pajak_06'] , 
			'pajak_07'=>  $ey['pajak_07'] , 
			'pajak_08'=>  $ey['pajak_08'] , 
			
			'pajak_09'=>  $ey['pajak_09'] , 
			'pajak_10'=>  $ey['pajak_10'] , 
			'pajak_11'=>  $ey['pajak_11'] , 
			'pajak_12'=>  $ey['pajak_12'] , 
			
			'pajak_13'=>  $ey['pajak_13'] , 
			'pajak_14'=>  $ey['pajak_14'] , 
			'pajak_15'=>  $ey['pajak_15'] , 
			'pajak_16'=>  $ey['pajak_16'] , 
			
			'pajak_17'=>  $ey['pajak_17'] , 
			'pajak_18'=>  $ey['pajak_18'] , 
			'pajak_19'=>  $ey['pajak_19'] , 
			'pajak_20'=>  $ey['pajak_20'] , 
			
			
			'pajak_21'=>  $ey['pajak_21'] , 
			'pajak_22'=>  $ey['pajak_22'] , 
			'pajak_23'=>  $ey['pajak_23'] , 
		);
		 		

	}
	
	$datas = table_rows_excel($row); 
	return table_builder_excel($header , $datas , 37 ,false ); 
}